package spring.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import spring.bean.Person;

public class JdbcTemplateEventDaoImpl implements JdbcTemplateEventDao {

	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	private JdbcTemplate jdbcTemplate;
	private SimpleJdbcTemplate simpleJdbcTemplate;
	private DataSource dataSource;
	private SimpleJdbcInsert insertJdbc;


	public void init(){
		namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
		jdbcTemplate = new JdbcTemplate(dataSource);
		simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
		insertJdbc=new SimpleJdbcInsert(dataSource).withTableName("t_person");
	}

	public int countOfObject(Person person) {
		String sql = "select count(0) from t_person where name=:name";
		SqlParameterSource namePara = new BeanPropertySqlParameterSource(person);
		return this.namedParameterJdbcTemplate.queryForInt(sql, namePara);
	}

	public Person findPerson(long id) {

		String sql = "select * from t_person where id=?";
		RowMapper mapper = new RowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				Person person = new Person();
				person.setName(rs.getString("name"));
				person.setDes(rs.getString("des"));
				return person;
			}
		};
		return (Person) jdbcTemplate.queryForObject(sql, new Long[] { id },
				mapper);
	}

	public Person findPersonSimple(long id) {

		String sql = "select * from t_person where id=?";
		ParameterizedRowMapper<Person> mapper = new ParameterizedRowMapper<Person>() {

			public Person mapRow(ResultSet rs, int rowNum) throws SQLException {
				Person person = new Person();
				person.setName(rs.getString("name"));
				person.setDes(rs.getString("des"));
				return person;
			}

		};
		return (Person) simpleJdbcTemplate.queryForObject(sql, mapper, id);
	}

	public void insert() {

		final String sql = "insert into t_person (name) values(?)";
		final String name = "李龙";
		KeyHolder holder = new GeneratedKeyHolder();

		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql,
						new String[] { "id" });
				ps.setString(1, name);
				return ps;
			}
		}, holder);
		System.out.println(holder.getKey());
	}
	public void help(){
//		SmartDataSource
	}
	@SuppressWarnings("unchecked")
	public List queryForList(){
		String sql="select * from t_person";
		return this.jdbcTemplate.queryForList(sql);
	}
	
	public void addMap(){
		Map<String, Object> map=new HashMap<String, Object>();
//		map.put("id", 3);
		map.put("name", "xiaochen");
		map.put("des", "gozap");
		insertJdbc.execute(map);
	}
	public void addBean(Person person){
		SqlParameterSource parameters = new BeanPropertySqlParameterSource(person);

		int i=insertJdbc.execute(parameters);
		System.out.println(i);
	}
	

	public DataSource getDataSource() {
		return dataSource;
	}
	public void setDataSource(DataSource dataSource) {
		this.dataSource=dataSource;
	}
	
}
